﻿using IPTVRClient.Utils;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IPTVRClient.dao
{
    /// <summary>
    /// 数据库操作工具类
    /// </summary>
    class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string connStr = Global.GetMysqlConnStr();

        /// <summary>
        /// 数据库连接
        /// </summary>
        private static SqlHelper sqlHelper = null;


        private static MySqlConnection mySqlConnection;

        private static bool IsCanConnectioned = false;

        /// <summary>
        /// 获取数据库连接，单例模式
        /// </summary>
        /// <returns></returns>
        public static SqlHelper getInstance()
        {
            if (null == sqlHelper)
            {
                sqlHelper = new SqlHelper();
            }

            return sqlHelper;
        }

        /// <summary>
        /// 数据库连接检测
        /// </summary>
        /// <returns></returns>
        public static bool ConnectionTest(string conn)
        {
            if (conn.Length > 0)
            {
                //创建连接对象
                mySqlConnection = new MySqlConnection(conn);
            }
            else
            {
                //创建连接对象
                mySqlConnection = new MySqlConnection(connStr);
            }
            
            try
            {
                //打开数据库
                mySqlConnection.Open();
                IsCanConnectioned = true;
            }
            catch
            {
                //Can not Open DataBase
                //打开不成功 则连接不成功
                IsCanConnectioned = false;
            }
            finally
            {
                //Close DataBase
                //关闭数据库连接
                mySqlConnection.Close();
            }

            if (mySqlConnection.State == ConnectionState.Closed || mySqlConnection.State == ConnectionState.Broken)
            {
                //Connection   is   not   available  
                return IsCanConnectioned;
            }
            else
            {
                //Connection   is   available  
                return IsCanConnectioned;
            }
        }
        
        /// <summary>
        /// 执行增删改查操作,无参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回影响的行数</returns>
        public static int excuteNonQuery(string sql)
        {
            return excuteNonQuery(sql,null);
        }

        /// <summary>
        /// 执行sql，带事物
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdparams"></param>
        private static void prepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdparams)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            // 设置连接语句
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (null != trans)
            {
                cmd.Transaction = trans;
            }

            if (null != cmdparams)
            {
                foreach (MySqlParameter param in cmdparams)
                {
                    cmd.Parameters.Add(param);
                }
            }
        }

        /// <summary>
        /// 执行sql，不带事物
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdparams"></param>
        private static void prepareCommand(MySqlCommand cmd, MySqlConnection conn, string cmdText, MySqlParameter[] cmdparams)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            // 设置连接语句
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (null != cmdparams)
            {
                foreach (MySqlParameter param in cmdparams)
                {
                    cmd.Parameters.Add(param);
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        private static void closeConn(MySqlCommand cmd, MySqlConnection conn)
        {
            // 清除参数
            if (null != cmd)
            {
                cmd.Parameters.Clear();
            }

            if (null != conn && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }


        /// <summary>
        /// 执行增删改查操作，带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int excuteNonQuery(string sql, params MySqlParameter[] param)
        {
            // 命令对象
            MySqlCommand sqlCommand = new MySqlCommand();

            // 连接对象
            MySqlConnection conn = new MySqlConnection(connStr);
            
            // 命令类型设置成数据库脚本
            sqlCommand.CommandType = CommandType.Text;

            //准备执行命令
            prepareCommand(sqlCommand, conn, sql, param);
            
            // 执行sql 
            int val = sqlCommand.ExecuteNonQuery();

            // 关闭连接
            closeConn(sqlCommand, conn);

            // 返回结果
            return val;
        }

        /// <summary>
        /// 执行查询操作，无参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet excuteQuery(string sql)
        {
            return excuteQuery(sql,null);
        }

        /// <summary>
        /// 执行查询操作，有参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataSet excuteQuery(string sql, params MySqlParameter[] param)
        {
            // 命令对象
            MySqlCommand sqlCommand = new MySqlCommand();

            // 连接对象
            MySqlConnection conn = new MySqlConnection(connStr);

            // 命令类型设置成数据库脚本
            sqlCommand.CommandType = CommandType.Text;

            //准备执行命令
            prepareCommand(sqlCommand, conn, sql, param);

            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = sqlCommand;

            DataSet ds = new DataSet();
            adapter.Fill(ds);

            // 关闭连接
            closeConn(sqlCommand, conn);
            return ds;
        }

        /// <summary>
        /// 返回查询到的数量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int excuteCountQuery(string sql)
        {
            // 命令对象
            MySqlCommand sqlCommand = new MySqlCommand();

            // 连接对象
            MySqlConnection conn = new MySqlConnection(connStr);

            // 命令类型设置成数据库脚本
            sqlCommand.CommandType = CommandType.Text;

            //准备执行命令
            prepareCommand(sqlCommand, conn, sql, null);

            int count = Convert.ToInt32(sqlCommand.ExecuteScalar());

            // 关闭连接
            closeConn(sqlCommand, conn);

            return count;
        }

        /// <summary>
        /// 返回查询到的数量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static int excuteCountQuery(String sql, params MySqlParameter[] param)
        {
            object obj = excuteScalarQuery(sql, param);

            if (obj.GetType() != typeof(DBNull))
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }


        /// <summary>
        /// 返回查询到的首行首列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object excuteScalarQuery(string sql)
        {
            return excuteScalarQuery(sql, null);
        }

        /// <summary>
        /// 返回查询到的首行首列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object excuteScalarQuery(String sql, params MySqlParameter[] param)
        {
            // 命令对象
            MySqlCommand sqlCommand = new MySqlCommand();

            // 连接对象
            MySqlConnection conn = new MySqlConnection(connStr);

            // 命令类型设置成数据库脚本
            sqlCommand.CommandType = CommandType.Text;

            //准备执行命令
            prepareCommand(sqlCommand, conn, sql, param);

            object obj = sqlCommand.ExecuteScalar();

            // 关闭连接
            closeConn(sqlCommand, conn);

            return obj;
        }
    }
}
